package BUS;

import java.awt.List;
import java.io.File;
import java.io.FileOutputStream;
import java.io.ObjectOutputStream;
import java.util.StringTokenizer;
import java.util.Vector;

import DAO.ColInfoDAO;
import DAO.DatabaseDAO;
import DAO.RecordDAO;
import DAO.TableDAO;
import DTO.ColInfoDTO;
import DTO.DatabaseDTO;
import DTO.RecordDTO;
import DTO.TableDTO;
import GUI.MainScreen;

public class QueryProccessor {
	private String _SqlStr;

	private enum SqlType {
		INSERT, DELETE, UPDATE, CREATE, SELECT, INVALID_STATEMENT
	}

	private static String[] _Operator = { "<>", ">=", "<=", ">", "<", "=" };
	private SqlType _SqlType;

	public QueryProccessor(String sqlStr) {
		_SqlStr = sqlStr;
		_SqlType = SqlType.INVALID_STATEMENT;
	}

	public Object excuteQuey() throws Exception {
		String sqlStr = _SqlStr.toUpperCase();
		if (sqlStr.startsWith("INSERT INTO "))
			_SqlType = SqlType.INSERT;
		if (sqlStr.startsWith("DELETE FROM "))
			_SqlType = SqlType.DELETE;
		if (sqlStr.startsWith("UPDATE "))
			_SqlType = SqlType.UPDATE;
		if (sqlStr.startsWith("CREATE "))
			_SqlType = SqlType.CREATE;
		if (sqlStr.startsWith("SELECT "))
			_SqlType = SqlType.SELECT;
		switch (_SqlType) {
		case INSERT:
			InsertIntoDatabase(_SqlStr);
			break;
		case DELETE:
			DeleteFromDatabase(_SqlStr);
			break;
		case UPDATE:
			UpdateTableFromDatabase(_SqlStr);
			break;
		case CREATE:
			break;
		case SELECT: {
			TableDTO table = ExcuteSelectQuery(_SqlStr);
			System.out.println("So record: " + table.get_recordList().size());
			return table;
		}
		default:
			throw new Exception("Invalid statement!");
		}
		return null;
	}

	private TableDTO ExcuteSelectQuery(String _SqlStr) throws Exception {
		// TODO Auto-generated method stub
		TableDTO result = null;
		String sql = _SqlStr.trim().toUpperCase();
		sql = normalizeString(sql);
		Vector<String> selectSQL = new Vector<String>();
		String[] selectStrings = sql.split("SELECT ");
		if (selectStrings.length > 3)
			throw new Exception("More than 2 SELECT !");
		selectSQL.add(selectStrings[1]);
		if (selectStrings.length == 3) {
			selectSQL.add(selectStrings[2]);
		}

		String[] temp = selectSQL.elementAt(0).split(" FROM ");
		if (temp.length > 2)
			throw new Exception("Invalid Command");
		String selectInput = temp[0];
		String fromInput;
		String whereInput = "";
		if (!temp[1].contains(" WHERE ")) {
			fromInput = temp[1];
		} else {
			String[] tmp = temp[1].split(" WHERE ");
			fromInput = tmp[0];
			whereInput = tmp[1];
		}
		DatabaseDAO databaseDAO = new DatabaseDAO(MainScreen._DATABASE_PATH);
		DatabaseDTO databaseDTO = databaseDAO.getDatabase();
		result = databaseDTO.selectQuery(selectInput, fromInput, whereInput);

		return result;
	}

	private String normalizeString(String sql) {
		// TODO Auto-generated method stub
		String result = sql;
		while (result.contains("  ")) {
			result = result.replaceAll("  ", " ");
		}
		return result;
	}

	private void UpdateTableFromDatabase(String sqlStr) throws Exception {
		// TODO Auto-generated method stub
		// UPDATE CUSTOMER SET CITY = 'YANG' WHERE ID = 10 OR ID = 11 AND
		// FIRSTNAME = 'Bill'
		// _SqlStr =
		// "UPDATE CUSTOMER SET CITY = 'YANG' WHERE ID = 10 OR ID = 11 AND FIRSTNAME = 'Bill'";
		String tableName = "";
		Vector<String> setCols = new Vector<String>();
		Vector<String> setVals = new Vector<String>();
		Vector<String> whereCols = new Vector<String>();
		Vector<String> whereVals = new Vector<String>();
		Vector<Boolean> logicOperatorOrder = new Vector<Boolean>();
		Vector<String> opertors = new Vector<String>();

		String upperSqlStr = sqlStr.toUpperCase();
		int setPos = upperSqlStr.indexOf(" SET ");
		if (-1 == setPos) {
			throw new Exception("Missing SET");
		}
		tableName = upperSqlStr.substring(7, setPos).trim();
		int wherePos = upperSqlStr.indexOf(" WHERE ");

		String setStr = "";
		if (-1 == wherePos) {
			setStr = sqlStr.substring(setPos + 5);
		} else {
			setStr = sqlStr.substring(setPos + 5, wherePos);
			String whereStr = upperSqlStr.substring(wherePos + 7);
			opertors = processConditionString(whereStr, whereCols, whereVals,
					logicOperatorOrder);
		}
		StringTokenizer strTokenizer = new StringTokenizer(setStr, "=");
		while (strTokenizer.hasMoreTokens()) {
			setCols.add(strTokenizer.nextToken().trim());
			setVals.add(strTokenizer.nextToken().trim());
		}
		// Phan tich xong chuoi sql
		ColInfoDAO colInfoDAO = new ColInfoDAO(MainScreen._DATABASE_PATH);
		RecordDAO recordDAO = new RecordDAO(MainScreen._DATABASE_PATH);

		Vector<RecordDTO> recordList = recordDAO.getAllRecord(tableName);
		for (int i = 0; i < recordList.size(); i++) {
			RecordDTO oldRecord = recordList.elementAt(i);
			Boolean result = true;
			int index = -1;
			if (whereCols.size() > 0) {
				index = colInfoDAO.getColInfoIndex(tableName,
						whereCols.elementAt(0));
				String whereColVal = oldRecord.get_data()[index];
				String dataType = colInfoDAO.getColInfo(tableName, index)
						.get_dataType();
				result = compare(whereColVal, whereVals.elementAt(0), dataType,
						opertors.elementAt(0));
				for (int j = 0; j < logicOperatorOrder.size(); j++) {
					index = colInfoDAO.getColInfoIndex(tableName,
							whereCols.elementAt(j + 1));
					whereColVal = oldRecord.get_data()[index];
					dataType = colInfoDAO.getColInfo(tableName, index)
							.get_dataType();
					if (logicOperatorOrder.elementAt(j))// OR
						result = result
								|| compare(whereColVal,
										whereVals.elementAt(j + 1), dataType,
										opertors.elementAt(j + 1));
					else
						// AND
						result = result
								&& compare(whereColVal,
										whereVals.elementAt(j + 1), dataType,
										opertors.elementAt(j + 1));
				}
			}
			if (result) {
				RecordDTO newRecord = new RecordDTO(oldRecord);
				for (int j = 0; j < setCols.size(); j++) {
					index = colInfoDAO.getColInfoIndex(tableName,
							setCols.elementAt(j));
					newRecord.get_data()[index] = setVals.elementAt(j).replace(
							"'", "");
				}
				recordDAO.updateRecord(tableName, oldRecord, newRecord);
			}
		}
	}

	/**
	 * @param whereCols
	 * @param whereVals
	 * @param whereStr
	 */
	public static Vector<String> processConditionString(String whereStr,
			Vector<String> whereCols, Vector<String> whereVals,
			Vector<Boolean> operatorOrder) {
		// whereStr = "x.x = y.x AND x.x <> y.y OR x.x <= y.y AND x.x >= y.y";
		Vector<String> operators = new Vector<String>();
		// false is AND, true is OR
		Vector<String> conditionStrs = new Vector<String>();
		String[] andConditionStrs = whereStr.split(" AND ");
		for (int i = 0; i < andConditionStrs.length; i++) {
			String[] orCondtionStrs = andConditionStrs[i].split(" OR ");
			if (orCondtionStrs.length > 1) {
				for (int j = 0; j < orCondtionStrs.length; j++) {
					operatorOrder.add(true);// OR
					conditionStrs.add(orCondtionStrs[j]);
				}
				if (orCondtionStrs.length > 1) {
					operatorOrder.remove(operatorOrder.size() - 1);
				}
			} else {
				conditionStrs.add(orCondtionStrs[0]);
			}
			operatorOrder.add(false);// AND
		}
		for (int i = 0; i < conditionStrs.size(); i++) {
			for (int j = 0; j < _Operator.length; j++) {
				String[] temp = conditionStrs.elementAt(i).split(_Operator[j]);
				if (temp.length == 2) {
					whereCols.add(temp[0].trim());
					whereVals.add(temp[1].trim());
					operators.add(_Operator[j]);
					break;
				}
			}
		}
		if (operatorOrder.size() > 0) {
			operatorOrder.remove(operatorOrder.size() - 1);
		}
		return operators;
	}

	private void DeleteFromDatabase(String sqlStr) throws Exception {
		// TODO Auto-generated method stub
		// DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
		String upperSqlStr = sqlStr.toUpperCase().replace("'", "");// ba dao o
																	// day :))
		String tableName = "";
		Vector<String> whereCols = new Vector<String>();
		Vector<String> whereVals = new Vector<String>();
		Vector<Boolean> operatorOrder = new Vector<Boolean>();

		int wherePos = upperSqlStr.indexOf(" WHERE ");
		if (-1 == wherePos) {
			tableName = upperSqlStr.substring(12);
		} else {
			tableName = upperSqlStr.substring(12, wherePos).trim();
			String whereStr = upperSqlStr.substring(wherePos + 7);
			processConditionString(whereStr, whereCols, whereVals,
					operatorOrder);
		}
		// xl xong chuoi sql
		ColInfoDAO colInfoDAO = new ColInfoDAO(MainScreen._DATABASE_PATH);
		RecordDAO recordDAO = new RecordDAO(MainScreen._DATABASE_PATH);

		Vector<RecordDTO> recordList = recordDAO.getAllRecord(tableName);
		for (int i = 0; i < recordList.size(); i++) {
			RecordDTO record = recordList.elementAt(i);
			Boolean result = true;
			int index = -1;
			if (whereCols.size() > 0) {
				index = colInfoDAO.getColInfoIndex(tableName,
						whereCols.elementAt(0));
				if (index < 0 || index > record.get_data().length)
					throw new Exception("Invalid column name");
				String whereColVal = record.get_data()[index];
				if (whereColVal == null) {
					result = whereColVal == whereVals.elementAt(0);
				} else {
					result = whereColVal.toUpperCase().equals(
							whereVals.elementAt(0));
				}
				for (int j = 0; j < operatorOrder.size(); j++) {
					index = colInfoDAO.getColInfoIndex(tableName,
							whereCols.elementAt(j + 1));
					whereColVal = record.get_data()[index];
					if (operatorOrder.elementAt(j))// OR
						result = result
								|| whereColVal.toUpperCase().equals(
										whereVals.elementAt(j + 1));
					else
						// AND
						result = result
								&& whereColVal.toUpperCase().equals(
										whereVals.elementAt(j + 1));
				}
			}
			if (result) {
				recordDAO.deleteRecord(tableName, record);
			}
		}
	}

	private boolean isValidInsertCommand(String content) {
		// TODO Auto-generated method stub
		return false;
	}

	private void CreateDatabase(String sqlStr) {
		// TODO Auto-generated method stub
		sqlStr = sqlStr.trim();
		String[] strings = sqlStr.split(" ");

		DatabaseDTO database = new DatabaseDTO(strings[2]);
		FileOutputStream fos;
		try {
			String userDir = System.getProperty("user.dir");
			String path = userDir + "\\Databases";
			File file = new File(path);
			if (!file.exists())
				file.mkdir();
			fos = new FileOutputStream(path + "\\" + strings[2] + ".dat");
			ObjectOutputStream oos = new ObjectOutputStream(fos);
			oos.writeObject(database);
			oos.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private boolean isValidCreateCommand(String sqlStr) {
		// TODO Auto-generated method stub
		sqlStr = sqlStr.trim();
		String[] strings = sqlStr.split(" ");
		if (strings.length == 3) {
			if (strings[0].equals("create") && strings[1].equals("database"))
				return true;
		}
		return false;
	}

	private void InsertIntoDatabase(String sqlStr) throws Exception {
		// TODO Auto-generated method stub
		// Result after process
		Boolean isFullStatement = false;
		String tableName = "";
		String[] colsName = null;
		String[] values = null;
		//
		String upperSqlStr = sqlStr.toUpperCase();
		if (!upperSqlStr.startsWith("INSERT INTO")) {
			throw new Exception("Invalid INSERT Command");
		}
		if (upperSqlStr.indexOf(" VALUES") == -1) {
			throw new Exception("Missing VALUES");
		}
		int intoPos = upperSqlStr.indexOf(" INTO ");
		int bracketPos = upperSqlStr.indexOf("(");
		if (upperSqlStr.lastIndexOf("(") != bracketPos) {
			isFullStatement = true;
		}
		int lastbracketPos;
		StringTokenizer tokenizer;
		lastbracketPos = upperSqlStr.indexOf(")");
		int valuePos = upperSqlStr.indexOf("VALUES");
		if (isFullStatement) {
			tableName = upperSqlStr.substring(intoPos + 6, bracketPos).trim();
			Vector<String> colsVector = new Vector<String>();
			tokenizer = new StringTokenizer(upperSqlStr.substring(
					bracketPos + 1, lastbracketPos), ",");
			while (tokenizer.hasMoreTokens()) {
				colsVector.add(tokenizer.nextToken().trim());
			}
			colsName = new String[colsVector.size()];
			colsVector.copyInto(colsName);
		} else {
			tableName = upperSqlStr.substring(intoPos + 6, valuePos).trim();
		}
		String endStatement = sqlStr.substring(valuePos + 6).trim();
		bracketPos = endStatement.indexOf("(");
		lastbracketPos = endStatement.indexOf(")");
		Vector<String> valuesVector = new Vector<String>();
		tokenizer = new StringTokenizer(endStatement.substring(bracketPos + 1,
				lastbracketPos), ",");
		while (tokenizer.hasMoreTokens()) {
			String value = tokenizer.nextToken();// .replace("'", "");
			value = value.trim();
			valuesVector.add(value);
		}
		values = new String[valuesVector.size()];
		valuesVector.copyInto(values);

		RecordDAO recordDAO = new RecordDAO(MainScreen._DATABASE_PATH);
		RecordDTO newRecord = null;
		ColInfoDAO colInfoDAO = new ColInfoDAO(MainScreen._DATABASE_PATH);
		int colCount = colInfoDAO.getAllColInfo(tableName).size();
		if (!isFullStatement) {

			if (colCount != values.length)
				throw new Exception("Invalid data");
			newRecord = new RecordDTO(colCount, new String[colCount]);
			for (int j = 0; j < colCount; j++) {
				ColInfoDTO colInfo = colInfoDAO.getColInfo(tableName, j);
				String dataType = colInfo.get_dataType();
				// if(dataType.equalsIgnoreCase(ColInfoDTO.FLOAT))
				// {
				// if(!isFloat(values[j]))
				// throw new Exception("Value \"" + values[j] +
				// "\" is invalid! It must be a float value");
				// }
				// if(dataType.equalsIgnoreCase(ColInfoDTO.STRING))
				// {
				// if(!isString(values[j]))
				// throw new Exception("Value \"" + values[j] +
				// "\" is invalid! It must be a string value");
				// }
				newRecord.get_data()[j] = values[j];// .replaceAll("'", "");
			}
		} else {
			if (colsName.length != values.length)
				throw new Exception("Invalid values map to columns");
			newRecord = new RecordDTO(colCount, new String[colCount]);
			for (int j = 0; j < colsName.length; j++) {
				int index = colInfoDAO.getColInfoIndex(tableName, colsName[j]);
				if (index == -1)
					throw new Exception("column name: " + colsName[j]
							+ "not found");
				ColInfoDTO colInfo = colInfoDAO.getColInfo(tableName, index);
				String dataType = colInfo.get_dataType();
				if (dataType.equalsIgnoreCase(ColInfoDTO.FLOAT)) {
					if (!isFloat(values[j]))
						throw new Exception("Value \"" + values[j]
								+ "\" is invalid! It must be a float value");
				}
				if (dataType.equalsIgnoreCase(ColInfoDTO.STRING)) {
					if (!isString(values[j]))
						throw new Exception("Value \"" + values[j]
								+ "\" is invalid! It must be a string value");
				}
				newRecord.get_data()[index] = values[j];// .replaceAll("'", "");
			}
		}
		recordDAO.insertRecord(tableName, newRecord);
	}

	public static Boolean isString(String str) {
		if (str == null)
			return true;
		if (str.length() <= 0)
			return false;
		// if(str.charAt(0) != '\'')
		// return false;
		// if(str.charAt(str.length() - 1) != '\'')
		// return false;
		return true;
	}

	public static Boolean isFloat(String str) {
		try {
			Float.parseFloat(str);
			return true;
		} catch (NumberFormatException ex) {
			return false;
		}
	}

	public static Boolean compare(String val1, String val2, String type,
			String operator) throws Exception {
		if (val1 == null || val2 == null)
			return false;
		if (type.equalsIgnoreCase("STRING")) {
			if (isString(val2))
				val2 = val2.replace("'", "");
			if (operator.equals("=")) {
				return val1.equalsIgnoreCase(val2);
			} else if (operator.equals(">")) {
				return val1.compareToIgnoreCase(val2) > 0 ? true : false;
			} else if (operator.equals("<")) {
				return val1.compareToIgnoreCase(val2) > 0 ? false : true;
			} else if (operator.equals("<>")) {
				return !val1.equalsIgnoreCase(val2);
			} else if (operator.equals(">=")) {
				return (val1.compareToIgnoreCase(val2) > 0 ? true : false)
						|| val1.equalsIgnoreCase(val2);
			} else if (operator.equals("<=")) {
				return (val1.compareToIgnoreCase(val2) > 0 ? false : true)
						|| val1.equalsIgnoreCase(val2);
			}

		} else if (type.equalsIgnoreCase("FLOAT")) {
			float v1, v2;
			try {
				v1 = Float.parseFloat(val1);
				v2 = Float.parseFloat(val2);
			} catch (Exception e) {
				throw new Exception("Invalid values");
			}
			if (operator.equals("=")) {
				return v1 == v2;
			} else if (operator.equals(">")) {
				return v1 > v2;
			} else if (operator.equals("<")) {
				return v1 < v2;
			} else if (operator.equals("<>")) {
				return v1 != v2;
			} else if (operator.equals(">=")) {
				return v1 >= v2;
			} else if (operator.equals("<=")) {
				return v1 <= v2;
			}
		}
		return false;
	}

	public static String getType(String strWhereValue,
			Vector<String> tableNames, Vector<String> tableVariables)
			throws Exception {
		// TODO Auto-generated method stub

		String tableName = "";
		String dataType = "float";
		String[] temp = strWhereValue.split(".");
		if (temp.length == 2) {
			for (int i = 0; i < tableVariables.size(); i++) {
				if (temp[0].equalsIgnoreCase(tableVariables.elementAt(i)))
					tableName = tableNames.elementAt(i);
			}
			
			DatabaseDAO dbDAO = new DatabaseDAO(MainScreen._DATABASE_PATH);
			DatabaseDTO dbDTO = dbDAO.getDatabase();
			Vector<TableDTO> listTables = dbDTO.get_tableList();
			for (int i = 0; i < listTables.size(); i++) {
				if(listTables.elementAt(i).get_name().equalsIgnoreCase(tableName)){
					int index = listTables.elementAt(i).getColIndex(temp[1]);
					if(index == -1)
						dataType = "string";
				}
			}
		} else if (temp.length == 1) {
			
		}
		
		return dataType;
	}
}
